package ystp.service;


import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;
import ystp.dao.KangjunyaowujieguoMapper;
import ystp.model.Kangjunyaowujieguo;
import ystp.model.KangjunyaowujieguoExample;
import ystp.model.Ysuser;
import ystp.support.StringHandle;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class KangjunyaowujieguoService {

    @Resource
    KangjunyaowujieguoMapper ddMapper;

    @Resource
    KangjunyaowuService ddKangjunyaowuService;

    @Resource
    YsuserService ddYsuserService;

    public void daochuqianzibiao(@RequestParam("xiangmuid") String ppxiangmuid,
                                 @RequestParam("userid") String ppuserid,
                                 HttpServletRequest request, HttpServletResponse response){

        response.reset();
        response.setContentType("application/x-download;charset=utf-8");
        List<Map<String,Object>> mmList = ddKangjunyaowuService.findDaochuPinzhongAll(ppxiangmuid);
        Ysuser mmuser=ddYsuserService.findOne(ppuserid);
        String mmusername=mmuser.getUsername();
        OutputStream os=null;
        WritableWorkbook wbook=null;
        try
        {
            os = new BufferedOutputStream(response.getOutputStream());//得到输出流的语句
            //response.getOutputStream();// 取得输出流
            response.reset();// 清空输出流
            SimpleDateFormat mmDateFormat = new SimpleDateFormat("yyyyMMdd-HHmmss");
            String wenjiantitle = "抗菌药物目录"+mmusername+"委员结果表"+ StringHandle.tiemString(); //文件名
            String tmptitle = "东部战区总医院抗菌药物目录投票表（"+mmusername+"）"; // 标题
            response.setHeader("Content-disposition", "attachment; filename="+new String(wenjiantitle.getBytes(),"iso8859-1")+".xls");// 设定输出文件头
            response.setContentType("application/vnd.ms-excel");// 定义输出类型
            wbook = Workbook.createWorkbook(os); // 建立excel文件
            WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
            SheetSettings settings=wsheet.getSettings();
            settings.setProtected(true);
            /*  settings.setPassword("0000");*/
            // 设置excel标题
            WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式

            WritableCellFormat wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); // 添加边框
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);

            WritableCellFormat wcfFC3 = new WritableCellFormat(wfont);
            wcfFC3.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC3.setAlignment(Alignment.CENTRE);
            //wcfFC.setBackground(Colour.AQUA);
            Label label=new Label(0, 0, tmptitle, wcfFC3);

            WritableCellFormat cellFormat=new WritableCellFormat();
            cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
            cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            cellFormat.setWrap(true);
            //cellFormat.setBackground(Colour.AQUA);
            cellFormat.setFont(wfont);
            label.setCellFormat(cellFormat);
            wsheet.addCell(label);
            wsheet.addCell(new Label(0, 0, tmptitle, wcfFC3));
            /*wsheet.addCell(new Label(7, 0, tmptitle, wcfFC));*/
            wsheet.setRowView(0,600); //第一行高度
            wsheet.setRowView(1,600); //第二行高度
            wsheet.mergeCells(0, 0, 18, 0);  //合并单元格(第一列的第一行和第十三列的第一行合并)
            wsheet.setColumnView(0,5); //宽度
            wsheet.setColumnView(1,5); //宽度
            wsheet.setColumnView(2,12); //药品名称宽度
            wsheet.setColumnView(3,12); //分类宽度
            wsheet.setColumnView(4,9); //规格宽度
            wsheet.setColumnView(5,4); //单位宽度
            wsheet.setColumnView(6,4); //医保宽度
            wsheet.setColumnView(7,4); //宽度
            wsheet.setColumnView(8,5); //宽度
            wsheet.setColumnView(9,5); //宽度
            wsheet.setColumnView(10,5); //宽度
            wsheet.setColumnView(11,5); //宽度
            wsheet.setColumnView(12,5); //宽度
            wsheet.setColumnView(13,6); //院本部宽度
            wsheet.setColumnView(14,6); //宽度
            wsheet.setColumnView(15,10); //宽度
            wsheet.setColumnView(16,10); //宽度
            wsheet.setColumnView(17,10); //宽度
            wsheet.setColumnView(18,10); //投票意见宽度




            // 开始生成主体内容
            wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);
            // 不需要边框和居中的样式
            WritableFont wfont2 = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); //设置表格样式
            WritableCellFormat wcfFC2 = new WritableCellFormat(wfont2);
            wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 垂直居中
            wcfFC2 = new WritableCellFormat(wfont2);
            wcfFC2.setWrap(true);
            //  在  1 列  2 行 写入内容
            wsheet.addCell(new Label(0, 1, "投票人(签字):投票内容已核对无误。"+mmusername,wcfFC2));
            // 把  0 列 2 行 到 9 列 2 行合并
            wsheet.mergeCells(0, 1, 12, 1);


            wsheet.addCell(new Label(13, 1, "投票日期:"+StringHandle.shorttiemString(),wcfFC2));
            wsheet.mergeCells(13, 1, 18, 1);

            // 第三行 内容
            wsheet.addCell(new Label(0, 2, "品种",wcfFC));
            wsheet.mergeCells(0, 2, 0, 3);
            wsheet.addCell(new Label(1, 2, "品规",wcfFC));
            wsheet.mergeCells(1, 2, 1, 3);
            wsheet.addCell(new Label(2, 2, "药品名称",wcfFC));
            wsheet.mergeCells(2, 2, 2, 3);
            wsheet.addCell(new Label(3, 2, "分类",wcfFC));
            wsheet.mergeCells(3, 2, 3, 3);
            wsheet.addCell(new Label(4, 2, "规格",wcfFC));
            wsheet.mergeCells(4, 2, 4, 3);
            wsheet.addCell(new Label(5, 2, "单位",wcfFC));
            wsheet.mergeCells(5, 2, 5, 3);
            wsheet.addCell(new Label(6, 2, "医保类别",wcfFC));
            wsheet.mergeCells(6, 2, 6, 3);

            wsheet.addCell(new Label(7, 2, "推荐依据",wcfFC));
            wsheet.mergeCells(7, 2, 10, 2);

            wsheet.addCell(new Label(11, 2, "所在目录",wcfFC));
            wsheet.mergeCells(11, 2, 12, 2);

            wsheet.addCell(new Label(13, 2, "半年无采购记录",wcfFC));
            wsheet.mergeCells(13, 2, 14, 2);

            wsheet.addCell(new Label(15, 2, "备注",wcfFC));
            wsheet.mergeCells(15, 2, 15, 3);

            wsheet.addCell(new Label(16, 2, "抗菌药物管理工作小组预选意见",wcfFC));
            wsheet.mergeCells(16, 2, 17, 2);

            wsheet.addCell(new Label(18, 2, "表决意见",wcfFC));
            wsheet.mergeCells(18, 2, 18, 3);
            // 第四行内容
            wsheet.addCell(new Label(7, 3, "基药",wcfFC));
            wsheet.addCell(new Label(8, 3, "集采药",wcfFC));
            wsheet.addCell(new Label(9, 3, "一致性评价",wcfFC));
            wsheet.addCell(new Label(10, 3, "15版指导原则",wcfFC));
            wsheet.addCell(new Label(11, 3, "院本部",wcfFC));
            wsheet.addCell(new Label(12, 3, "秦淮",wcfFC));
            wsheet.addCell(new Label(13, 3, "院本部",wcfFC));
            wsheet.addCell(new Label(14, 3, "秦淮",wcfFC));
            wsheet.addCell(new Label(16, 3, "是否为前48个品种",wcfFC));
            wsheet.addCell(new Label(17, 3, "品规得票（总票数25）",wcfFC));

            // 表头结束

            wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 10,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            wcfFC = new WritableCellFormat(wfont);
            wcfFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);   //设置居中对齐
            wcfFC.setAlignment(Alignment.CENTRE);
            wcfFC.setWrap(true);

            int count=mmList.size();
            int mmpinzhongKaishihang=4;
            int mmpinpaiKaishihang=4;


            for(int i=0;i<count;i++){
                Map<String,Object> mmPinzhongMap=mmList.get(i);

                String mmPinzhongOne = mmPinzhongMap.get("pinzhong") == null ? "" : mmPinzhongMap.get("pinzhong").toString();
                int mmHebingshu = 0;

                List<Map<String,Object>> mmPinguilist = ddKangjunyaowuService.findPinguiResultByPinzhong(ppxiangmuid,ppuserid,mmPinzhongOne,"","","","","","");
                if (mmPinguilist.size()>0){
                    mmHebingshu = mmPinguilist.size()-1;
                }
                for (int j = 0; j < mmPinguilist.size(); j++) {
                    Map<String,Object> mmPinguiMap = mmPinguilist.get(j);
                    if (mmPinguiMap!=null){
                        String mmpingui = mmPinguiMap.get("pingui")==null ? "" :mmPinguiMap.get("pingui").toString();
                        String mmyaopinmingcheng=mmPinguiMap.get("yaopinmingcheng")==null ? "" :mmPinguiMap.get("yaopinmingcheng").toString();
                        String mmfenlei=mmPinguiMap.get("fenlei")==null ? "" :mmPinguiMap.get("fenlei").toString();
                        String mmguige=mmPinguiMap.get("guige")==null ? "" :mmPinguiMap.get("guige").toString();
                        String mmdanwei=mmPinguiMap.get("danwei")==null ? "" :mmPinguiMap.get("danwei").toString();
                        String mmjiage=mmPinguiMap.get("jiage")==null ? "" :mmPinguiMap.get("jiage").toString();
                        String mmyibaoleibie=mmPinguiMap.get("yibaoleibie")==null ? "" :mmPinguiMap.get("yibaoleibie").toString();
                        String mmguojiajibenyaowu=mmPinguiMap.get("guojiajibenyaowu")==null ? "" :mmPinguiMap.get("guojiajibenyaowu").toString();
                        String mmjicaiyao=mmPinguiMap.get("jicaiyao")==null ? "" :mmPinguiMap.get("jicaiyao").toString();
                        String mmtongguopingjia=mmPinguiMap.get("tongguopingjia")==null ? "" :mmPinguiMap.get("tongguopingjia").toString();
                        String mmzhidaoyuanze=mmPinguiMap.get("zhidaoyuanze")==null ? "" :mmPinguiMap.get("zhidaoyuanze").toString();
                        String mmdongzongyao=mmPinguiMap.get("dongzongyao")==null ? "" :mmPinguiMap.get("dongzongyao").toString();
                        String mmdongzongjilu2=mmPinguiMap.get("dongzongjilu2")==null ? "" :mmPinguiMap.get("dongzongjilu2").toString();
                        String mmqinhuaiyao=mmPinguiMap.get("qinhuaiyao")==null ? "" :mmPinguiMap.get("qinhuaiyao").toString();
                        String mmqinhuaijilu2=mmPinguiMap.get("qinhuaijilu2")==null ? "" :mmPinguiMap.get("qinhuaijilu2").toString();
                        String mmzhidaoyijian=mmPinguiMap.get("zhidaoyijian")==null ? "" :mmPinguiMap.get("zhidaoyijian").toString();
                        String mmbeizhu=mmPinguiMap.get("beizhu")==null ? "" :mmPinguiMap.get("beizhu").toString();
                        String mmPinzhongpaiming=mmPinguiMap.get("pinzhongpaiming")==null ? "" :mmPinguiMap.get("pinzhongpaiming").toString();
                        String mmPinguipiaoshu=mmPinguiMap.get("pinguipiaoshu")==null ? "" :mmPinguiMap.get("pinguipiaoshu").toString();

                        String mmresult=mmPinguiMap.get("result")==null ? "" :mmPinguiMap.get("result").toString();
                        String mmbiaojueren=mmPinguiMap.get("username")==null ? "" :mmPinguiMap.get("username").toString();
                        String mmyijian="";
                        if("1".equals(mmresult)){
                            mmyijian="同意";
                        }else if("0".equals(mmresult)){
                            mmyijian="不同意";
                        }else {
                            mmyijian="不同意";
                        }
                        wsheet.addCell(new Label(1, mmpinpaiKaishihang,mmpingui,wcfFC));
                        wsheet.addCell(new Label(2, mmpinpaiKaishihang,mmyaopinmingcheng,wcfFC));
                        wsheet.addCell(new Label(3, mmpinpaiKaishihang,mmfenlei,wcfFC));
                        wsheet.addCell(new Label(4, mmpinpaiKaishihang,mmguige,wcfFC));
                        wsheet.addCell(new Label(5, mmpinpaiKaishihang,mmdanwei,wcfFC));
                        wsheet.addCell(new Label(6, mmpinpaiKaishihang,mmyibaoleibie,wcfFC));
                        wsheet.addCell(new Label(7, mmpinpaiKaishihang,mmguojiajibenyaowu,wcfFC));
                        wsheet.addCell(new Label(8, mmpinpaiKaishihang,mmjicaiyao,wcfFC));
                        wsheet.addCell(new Label(9, mmpinpaiKaishihang,mmtongguopingjia,wcfFC));
                        wsheet.addCell(new Label(10, mmpinpaiKaishihang,mmzhidaoyuanze,wcfFC));
                        wsheet.addCell(new Label(11, mmpinpaiKaishihang,mmdongzongyao,wcfFC));
                        wsheet.addCell(new Label(12, mmpinpaiKaishihang,mmqinhuaiyao,wcfFC));
                        wsheet.addCell(new Label(13, mmpinpaiKaishihang,mmdongzongjilu2,wcfFC));
                        wsheet.addCell(new Label(14, mmpinpaiKaishihang,mmqinhuaijilu2,wcfFC));
                        wsheet.addCell(new Label(15, mmpinpaiKaishihang,mmbeizhu,wcfFC));
                        wsheet.addCell(new Label(16, mmpinpaiKaishihang,mmPinzhongpaiming,wcfFC));
                        wsheet.addCell(new Label(17, mmpinpaiKaishihang,mmPinguipiaoshu,wcfFC));
                        wsheet.addCell(new Label(18, mmpinpaiKaishihang,mmyijian,wcfFC));
                        mmpinpaiKaishihang = mmpinpaiKaishihang+1;
                    }
                }
                wsheet.addCell(new Label(0, mmpinzhongKaishihang, mmPinzhongOne,wcfFC));
                wsheet.mergeCells(0, mmpinzhongKaishihang, 0, mmpinzhongKaishihang+mmHebingshu);
                mmpinzhongKaishihang = mmpinzhongKaishihang+mmHebingshu+1;

            }
            wbook.write(); // 写入文件
            wbook.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }finally{
            if(os!=null){
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }


    public List<Map<String,Object>> findByxiangmuId(String mmXiangmuId){
        return ddMapper.findByxiangmuId(mmXiangmuId);
    }

    public Integer getKangJunResultCount(String mmxiangmuId,String mmuserId,String mmresult){
        Object mmCount = ddMapper.getKangJunResultCount(mmxiangmuId,mmuserId,mmresult);
        return mmCount == null ? 0 : Integer.parseInt(mmCount.toString());
    }

    public List<Kangjunyaowujieguo> findByDetailIdAndUserId(String ppdetailid,String ppuserid){
        KangjunyaowujieguoExample mmExample=new KangjunyaowujieguoExample();
        KangjunyaowujieguoExample.Criteria criteria = mmExample.createCriteria();
        criteria.andDetailidEqualTo(ppdetailid).andUseridEqualTo(ppuserid);
        return ddMapper.selectByExample(mmExample);
    }
    public Kangjunyaowujieguo findone(String mmid){
        return ddMapper.selectByPrimaryKey(mmid);
    }


    public Integer getGuizeCount(String mmuserId,String mmxiangmuId,String mmguizebianhao){
        int count =ddMapper.getGuizeCount(mmuserId,mmxiangmuId,mmguizebianhao).size();
        return count;
    }

    public Integer getZiGuizeCount(String mmuserId,String mmxiangmuId,String mmziguizebianhao){
        int count =ddMapper.getZiGuizeCount(mmuserId,mmxiangmuId,mmziguizebianhao).size();
        return count;
    }

    public boolean add(Kangjunyaowujieguo kangjunyaowujieguo){return ddMapper.insert(kangjunyaowujieguo)==1;}
    public boolean modify(Kangjunyaowujieguo kangjunyaowujieguo){return ddMapper.updateByPrimaryKeySelective(kangjunyaowujieguo)==1;}

    public Kangjunyaowujieguo checkexistByUserId(int ppPinzhong,String ppPingui,String ppuserId){
        KangjunyaowujieguoExample mmExample=new KangjunyaowujieguoExample();
        KangjunyaowujieguoExample.Criteria criteria = mmExample.createCriteria();
        criteria.andPinzhongEqualTo(ppPinzhong).andPinguiEqualTo(ppPingui).andUseridEqualTo(ppuserId);
        return ddMapper.selectByExample(mmExample).size()>0? ddMapper.selectByExample(mmExample).get(0): null;
    }


    public List<Map<String,Object>> getPinzhongpaixu(String mmXiangmuId){
        return ddMapper.getPinzhongpaixu(mmXiangmuId);
    }

    public List<Map<String,Object>> getResultByPinzhongAndXiangmuId(String mmPinzhong,String mmXiangmuID,String mmUserId){
        return ddMapper.getResultByPinzhongAndXiangmuId(mmPinzhong,mmXiangmuID,mmUserId);
    }

    public List<Map<String,Object>> getResultByPinguiAndXiangmuId(String mmPingui,String mmXiangmuID){
        return ddMapper.getResultByPinguiAndXiangmuId(mmPingui,mmXiangmuID);
    }

    public List<Map<String,Object>> getVoitedPinzhongList(String mmXiangmuId,String mmUserId){
        return ddMapper.getVoitedPinzhongList(mmXiangmuId,mmUserId);
    }
    public List<Map<String,Object>> getVoitedFenleiList(String mmXiangmuId,String mmUserId,String mmFenlei){
        return ddMapper.getVoitedFenleiList(mmXiangmuId,mmUserId,mmFenlei);
    }

    public List<Map<String,Object>> findAgreedFenleiList(String mmXiangmuId,String mmUserId){
        return ddMapper.findAgreedFenleiList(mmXiangmuId,mmUserId);
    }

    public List<Map<String,Object>> getAgreedPinguiListByPinzhong(String mmXiangmuId,String mmPinzhong){
        return ddMapper.getAgreedPinguiListByPinzhong(mmXiangmuId,mmPinzhong);
    }

}
